#///////////////////////////////////////////////////////////////////////////////
##.. module:: retrieveData
#   
# Retrieve data from a remote server, and store in an SQLite database.
# By default, reads data every 45 minutes.
# 
# 
# Parameters
# !!!!!!!!!!!
# HTTP_Address
#   the public address of your device. 
#
# Command-line usage
# !!!!!!!!!!!!!!!!!!
# :: 
#    
#    python retrieveData.py http://xxx.xxx.xxx.xxx
#
# Pythonic usage
# !!!!!!!!!!!!!!
# :: 
# 
#   retrieveData("http://xxx.xxx.xxx.xxx")
#
## .. codeauthor:: Joe Kwiatkowski http://joejk.com
#///////////////////////////////////////////////////////////////////////////////
import sqlite3
import urllib
import time 
import sys

#CONFIGURATION:

def getDataRemote(httpAddress):
    return urllib.urlopen(httpAddress + \
                    "/history/secondhistory.csv?MTU=0&INDEX=0&U=1").readlines();

def getDataLocal():
    # TODO:
    return None


def retrieveData(httpAddress=None, getData=getDataRemote):
    if getData==getDataRemote and httpAddress==None:
        try:
            httpAddress = sys.argv[1]
        # TODO: throw exception here.
        except: 
            print "Please define httpAddress"
            return

    #CONNECT TO DATABASE.
    try:
        cxn=sqlite3.connect('ted5000.sqlite')
        cur = cxn.cursor()
    # TODO: throw exception here.
    except: 
        print 'Ooops - cannot connect to db.'        
    
    #CREATE TABLE, IF NECESSARY.
    try: 
        sql='CREATE TABLE data(dateTime  DATETIME ' + \
                    'PRIMARY KEY, kW FLOAT, V FLOAT)';
        print sql
        cur.execute(sql)
        cxn.commit()
    # TODO: throw exception here.
    except: 
        print 'Table exists.'
    
    
    #REPEATEDLY DOWNLOAD DATA AND STORE INTO DATABASE.
    while True:
        #Count existing records:
        countOld = int(cur.execute('SELECT COUNT(*) FROM data').fetchone()[0])
        #Retrieve data:
        data = getData(httpAddress)
        #Write data:
        for record in data[1:]: #(Discard labels in 1st record)
            record.strip()
            datum=record.split(',')
            #Reformat the date:
            _date, _time = datum[1].split()
            m, d, y = _date.split('/')
            _date = '-'.join([y,m,d])
            dateTime = ' '.join([_date, _time])
            #Insert, ignoring any duplicated records:
            sqlText = 'INSERT OR IGNORE INTO data VALUES(datetime("%s"), %s, %s)' %\
                    (dateTime, datum[2], datum[3])
            cur.execute(sqlText)
        cxn.commit()
        #Calculate number of new records:
        countNew = int(cur.execute('SELECT COUNT(*) FROM data').fetchone()[0])
        output = 'Added %d records at time ' % (countNew - countOld) +\
                 str(time.strftime("%a, %d %b %Y %H:%M:%S", time.localtime()))
        print output
        #Sleep for 45 minutes (TED 5000 stores an hour's worth of seconds data).
        time.sleep(2700)
    
    #CLOSE NICELY.
    cxn.close()
    #///////////////////////////////////////////////////////////////////////////

if __name__ == '__main__':
    retrieveData()
